package com.store.mapper;

import com.store.entity.Order;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface OrderMapper {
    
    @Select("SELECT * FROM `order` ORDER BY create_time DESC LIMIT #{offset}, #{pageSize}")
    List<Order> selectList(@Param("offset") int offset, @Param("pageSize") int pageSize);
    
    @Select("SELECT * FROM `order` ORDER BY create_time DESC")
    List<Order> selectAll();
    
    @Select("SELECT * FROM `order` WHERE order_id = #{orderId}")
    Order selectById(Long orderId);
    
    @Select("SELECT * FROM `order` WHERE user_id = #{userId} ORDER BY create_time DESC")
    List<Order> selectByUserId(Long userId);
    
    @Insert("INSERT INTO `order` (order_no, user_id, total_amount, status, shipping_address, " +
            "shipping_name, shipping_phone, product_id, product_name, product_price, quantity, " +
            "create_time, update_time) VALUES (#{orderNo}, #{userId}, #{totalAmount}, #{status}, " +
            "#{shippingAddress}, #{shippingName}, #{shippingPhone}, #{productId}, #{productName}, " +
            "#{productPrice}, #{quantity}, #{createTime}, #{updateTime})")
    @Options(useGeneratedKeys = true, keyProperty = "orderId")
    int insert(Order order);
    
    @Update("UPDATE `order` SET order_no = #{orderNo}, user_id = #{userId}, total_amount = #{totalAmount}, " +
            "status = #{status}, shipping_address = #{shippingAddress}, shipping_name = #{shippingName}, " +
            "shipping_phone = #{shippingPhone}, product_id = #{productId}, product_name = #{productName}, " +
            "product_price = #{productPrice}, quantity = #{quantity}, update_time = #{updateTime} " +
            "WHERE order_id = #{orderId}")
    int update(Order order);
    
    @Delete("DELETE FROM `order` WHERE order_id = #{orderId}")
    int deleteById(Long orderId);
    
    @Select("SELECT COUNT(*) FROM `order`")
    long count();
    
    @Select("SELECT * FROM `order` WHERE status = #{status} ORDER BY create_time DESC")
    List<Order> selectByStatus(String status);
    
    @Select("SELECT COUNT(*) FROM `order` WHERE status = #{status}")
    long countByStatus(String status);
    
    @Select("SELECT COUNT(*) FROM `order` WHERE create_time BETWEEN #{startDate} AND #{endDate}")
    long countByDateRange(@Param("startDate") String startDate, @Param("endDate") String endDate);
    
    @Select("SELECT COUNT(*) FROM `order` WHERE DATE(create_time) = #{date}")
    long countByDate(LocalDate date);
    
    @Select("SELECT SUM(total_amount) FROM `order` WHERE create_time BETWEEN #{startDate} AND #{endDate}")
    BigDecimal calculateSalesByDateRange(@Param("startDate") String startDate, @Param("endDate") String endDate);
    
    @Select("SELECT SUM(total_amount) FROM `order` WHERE DATE(create_time) = #{date}")
    BigDecimal calculateSalesByDate(LocalDate date);
    
    @Select("SELECT AVG(total_amount) FROM `order` WHERE create_time BETWEEN #{startDate} AND #{endDate}")
    BigDecimal calculateAverageOrderValueByDateRange(@Param("startDate") String startDate, @Param("endDate") String endDate);
    
    @Select("SELECT DATE(create_time) as date, SUM(total_amount) as amount " +
            "FROM `order` WHERE create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY DATE(create_time) ORDER BY date")
    Map<String, Object> selectSalesData(@Param("startDate") String startDate, @Param("endDate") String endDate);
    
    @Select("SELECT COALESCE(SUM(total_amount), 0) FROM `order` WHERE DATE(create_time) = #{date}")
    BigDecimal selectDailySales(LocalDate date);
    
    @Select("SELECT COALESCE(SUM(total_amount), 0) FROM `order` " +
            "WHERE YEAR(create_time) = #{date.getYear()} AND MONTH(create_time) = #{date.getMonthValue()}")
    BigDecimal selectMonthlySales(LocalDate date);
    
    @Select("SELECT COALESCE(SUM(total_amount), 0) FROM `order`")
    BigDecimal calculateTotalSales();
    
    @Select("SELECT * FROM `order` ORDER BY create_time DESC LIMIT #{limit}")
    List<Order> selectRecentOrders(int limit);
    
    @Select("SELECT p.*, COUNT(o.order_id) as order_count, SUM(o.total_amount) as total_sales " +
            "FROM `order` o " +
            "JOIN product p ON o.product_id = p.id " +
            "GROUP BY p.id " +
            "ORDER BY total_sales DESC " +
            "LIMIT #{limit}")
    List<Map<String, Object>> selectTopSellingProducts(int limit);
} 